<?php if (!defined('BASEPATH')) {
	exit('No direct script access allowed');
}

class Collectionprogress_model extends CI_Model {
	public function __construct() {
		parent::__construct();
	}

	public function get_status($date) {
		//var_dump($date);
		/*$query = $this->db->query("SELECT aea.zone_1 area,
										   count(*) custs_count,
									       count(wc.bill_total) bill_count,
									       ifnull(sum(wc.bill_total),0) bill_amt, 
									       count(wc.ac_amount) pay_count, 
										   count(*) - count(wc.ac_amount) pending_pay,
										   ifnull(sum(wc.ac_amount),0) pay_amt, 
										   ifnull(count(wc.ac_amount)*100/count(wc.bill_total),0) count_percent,
										   ifnull(sum(wc.ac_amount),0)*100/ifnull(sum(wc.bill_total),1) amt_percent
									FROM ar_account aa
					                INNER JOIN ar_entity_address aea ON aa.acct_addr = aea.address_id
					             	LEFT OUTER JOIN (select ucit.account_id account_id, SUM(ucit.bill_total) bill_total, SUM(apv.amount) ac_amount
													from ubs_cust_inv_total_v ucit
					                       LEFT OUTER JOIN ubs_payments_v apv ON ucit.invoice_id = apv.invoice_id
										  where DATE_FORMAT(ucit.latest_bill_date,'%Y-%m') = '".$date."'
										  and apv.amount = ucit.bill_total
					                        group by account_id) wc ON aa.account_id = wc.account_id
					                     WHERE LAST_DAY(STR_TO_DATE('".$date."', '%Y-%m')) BETWEEN LAST_DAY(aa.active_start) AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))
					                     AND aa.enable_flag = 'Y'
				GROUP BY aea.zone_1");*/
		/*$query = $this->db->query("SELECT aea.zone_1 area,
										   count(*) custs_count,
									       count(z.bill_total) bill_count,
									       ifnull(sum(z.bill_total) - sum(v.ac_amount),0) bill_amt,
									       count(v.ac_amount) pending_pay,
										   count(*) - count(v.ac_amount) pay_count,
										   ifnull(sum(z.bill_total - v.ac_amount) ,0) pay_amt, 
										   ifnull(count(v.ac_amount)*100/count(z.bill_total),0) count_percent,
										   ifnull(sum(v.ac_amount),0)*100/ifnull(sum(z.bill_total),1) amt_percent
									FROM ar_account aa
					                INNER JOIN ar_entity_address aea ON aa.acct_addr = aea.address_id
					             	LEFT JOIN (SELECT uv.account_id id,uv.bill_total bill_total
                                                FROM ubs_latest_payments_v uv
                                                WHERE DATE_FORMAT(uv.latest_bill_date,'%Y-%m') = '".$date."'
                                                GROUP BY id) z ON z.id = aa.account_id
                                    LEFT JOIN (SELECT uvs.latest_bill_date , uvs.account_id id,uvs.pay_total ac_amount 
                                                FROM ubs_latest_payments_v uvs
                                                WHERE uvs.bill_total <> uvs.pay_total
                                                AND DATE_FORMAT(uvs.latest_bill_date,'%Y-%m') = '".$date."'
                                                GROUP BY id) v ON v.id = aa.account_id
					                     WHERE LAST_DAY(STR_TO_DATE('".$date."', '%Y-%m'))
                                         BETWEEN LAST_DAY(aa.active_start)
                                         AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))
					                     AND aa.enable_flag = 'Y'
									GROUP BY aea.zone_1"); */
		/*$query = $this->db->query("SELECT aea.zone_1 area,
										   count(*) custs_count,
									       count(z.bill_total) bill_count,
									       ifnull(sum(z.bill_total),0) bill_amt,
									       count(v.ac_amount) pending_pay,
										   count(*) - count(v.ac_amount) pay_count,
										   ifnull(sum(v.ac_amount) ,0) pay_amt, 
										   ifnull(count(v.ac_amount)*100/count(z.bill_total),0) count_percent,
										   ifnull(sum(v.ac_amount),0)*100/ifnull(sum(z.bill_total),1) amt_percent
									FROM ar_account aa
					                INNER JOIN ar_entity_address aea ON aa.acct_addr = aea.address_id
					             	LEFT JOIN (SELECT ui.customer_id cust_id, ifnull(sum(uil.line_amt),0) bill_total FROM ubs_invoices ui 
                                               	          INNER JOIN ubs_invoice_lines uil ON uil.invoice_id = ui.invoice_id
                                               			  WHERE DATE_FORMAT(ui.invoice_date,'%Y-%m') = '".$date."'
                                               			  GROUP BY cust_id) z ON z.cust_id = aa.account_id
                                    LEFT JOIN (SELECT uvs.latest_bill_date , uvs.account_id id,uvs.pay_total ac_amount 
                                                FROM ubs_latest_payments_v uvs
                                                WHERE uvs.bill_total <> uvs.pay_total
                                                AND DATE_FORMAT(uvs.latest_bill_date,'%Y-%m') = '".$date."'
                                                GROUP BY id) v ON v.id = aa.account_id
					                     WHERE LAST_DAY(STR_TO_DATE('".$date."', '%Y-%m'))
                                         BETWEEN LAST_DAY(aa.active_start)
                                         AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))
					                     AND aa.enable_flag = 'Y'
									GROUP BY aea.zone_1");*/
		/*$query = $this->db->query("SELECT aea.zone_1 area,
										   count(*) custs_count,
									       count(z.bill_total) bill_count,
									       ifnull(sum(z.bill_total),0) bill_amt,
									       count(x.id) pending_pay,
										   count(v.ac_amount) pay_count,
										   ifnull(sum(v.ac_amount) ,0) pay_amt, 
										   ifnull(count(v.ac_amount)*100/count(z.bill_total),0) count_percent,
										   ifnull(sum(v.ac_amount),0)*100/ifnull(sum(z.bill_total),1) amt_percent
									FROM ar_account aa
					                INNER JOIN ar_entity_address aea ON aa.acct_addr = aea.address_id
					             	LEFT JOIN (SELECT ui.customer_id cust_id, ifnull(sum(uil.line_amt),0) bill_total FROM ubs_invoices ui 
                                               	          INNER JOIN ubs_invoice_lines uil ON uil.invoice_id = ui.invoice_id
                                               			  WHERE DATE_FORMAT(ui.invoice_date,'%Y-%m') = '".$date."'
                                               			  AND ui.invoice_num IN (SELECT bill_id FROM ubs_meter_readings)
                                               			  GROUP BY cust_id) z ON z.cust_id = aa.account_id
                                    LEFT JOIN (SELECT ap.account_id id,ifnull(sum(ap.sum_pay),0) ac_amount 
                                                FROM report_colprog_v ap
                                                WHERE ap.bill_total = ifnull(ap.sum_pay,0)
                                                AND DATE_FORMAT(ap.invoice_date,'%Y-%m') = '".$date."'
                                                GROUP BY id) v ON v.id = aa.account_id
									LEFT JOIN (SELECT ap.account_id id
                                                FROM report_colprog_v ap
                                                WHERE ap.bill_total <> ifnull(ap.sum_pay,0)
                                                AND DATE_FORMAT(ap.invoice_date,'%Y-%m') = '".$date."'
                                                GROUP BY id) x ON x.id = aa.account_id
					                     WHERE LAST_DAY(STR_TO_DATE('".$date."', '%Y-%m'))
                                         BETWEEN LAST_DAY(aa.active_start)
                                         AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))
					                     AND aa.enable_flag = 'Y'
								    GROUP BY aea.zone_1");*/
		/*$query = $this->db->query("SELECT aea.zone_1 area,
										   count(*) custs_count,
									       count(z.bill_total) bill_count,
									       ifnull(sum(z.bill_total),0) bill_amt,
									       count(x.id) pending_pay,
										   count(z.bill_total) - count(x.id) pay_count,
										   ifnull(sum(v.ac_amount) ,0) pay_amt, 
										   ifnull(count(v.ac_amount)*100/count(z.bill_total),0) count_percent,
										   ifnull(sum(v.ac_amount),0)*100/ifnull(sum(z.bill_total),1) amt_percent
									FROM ar_account aa
					                INNER JOIN ar_entity_address aea ON aa.acct_addr = aea.address_id
					             	LEFT JOIN (SELECT ui.customer_id cust_id, ifnull(sum(uil.line_amt),0) bill_total FROM ubs_invoices ui 
                                               	          INNER JOIN ubs_invoice_lines uil ON uil.invoice_id = ui.invoice_id
                                               			  WHERE DATE_FORMAT(ui.invoice_date,'%Y-%m') = '".$date."'
                                               			  AND ui.invoice_num IN (SELECT bill_id FROM ubs_meter_readings)
                                               			  GROUP BY cust_id) z ON z.cust_id = aa.account_id
                                    LEFT JOIN (SELECT ap.account_id id,ifnull(sum(ap.sum_pay),0) ac_amount 
                                                FROM report_colprog_v ap
                                                WHERE 1=1 -- ap.bill_total = ifnull(ap.sum_pay,0)
                                                AND DATE_FORMAT(ap.invoice_date,'%Y-%m') = '".$date."'
                                                GROUP BY id) v ON v.id = aa.account_id
									LEFT JOIN (SELECT ifnull(sum(uil.line_amt),0) curchg,ui.customer_id id FROM ubs_invoices ui 
                                                INNER JOIN ubs_invoice_lines uil ON uil.invoice_id = ui.invoice_id
                                                WHERE DATE_FORMAT(ui.invoice_date,'%Y-%m') = '".$date."'
                                                AND ui.invoice_num IN (SELECT bill_id FROM ubs_meter_readings)
                                                AND ui.invoice_id NOT IN (SELECT invoice_id FROM ar_payments GROUP BY account_id)
                                                GROUP BY ui.invoice_id) x ON x.id = aa.account_id
					                     WHERE LAST_DAY(STR_TO_DATE('".$date."', '%Y-%m'))
                                         BETWEEN LAST_DAY(aa.active_start)
                                         AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))
					                     AND aa.enable_flag = 'Y'
								    GROUP BY aea.zone_1");*/
		// $query = $this->db->query("SELECT aea.zone_1 area,
		// 								   count(*) custs_count,
		// 							       count(z.bill_total) bill_count,
		// 							       ifnull(sum(z.bill_total),0) bill_amt,
		// 							       count(x.id) pending_pay,
		// 								   count(z.bill_total) - count(x.id) pay_count,
		// 								   ifnull(sum(v.ac_amount) ,0) pay_amt, 
		// 								   ifnull(count(v.ac_amount)*100/count(z.bill_total),0) count_percent,
		// 								   ifnull(sum(v.ac_amount),0)*100/ifnull(sum(z.bill_total),1) amt_percent
		// 							FROM ar_account aa
		// 			                INNER JOIN ar_entity_address aea ON aa.acct_addr = aea.address_id
		// 			             	LEFT JOIN (SELECT ui.customer_id cust_id,ifnull(sum(uil.line_amt),0) bill_total FROM ubs_invoices ui 
  //                                              	          INNER JOIN ubs_invoice_lines uil ON uil.invoice_id = ui.invoice_id
  //                                              			  WHERE DATE_FORMAT(ui.invoice_date,'%Y-%m') = '".$date."'
  //                                              			  AND ui.invoice_num IN (SELECT bill_id FROM ubs_meter_readings)
  //                                              			  GROUP BY cust_id) z ON z.cust_id = aa.account_id
  //                                   LEFT JOIN (SELECT ap.account_id id,ifnull(sum(ap.bill_total),0) bill_total,ifnull(sum(ap.sum_pay),0) ac_amount 
  //                                               FROM report_colprog_v ap
  //                                               WHERE 1=1 -- ap.bill_total = ifnull(ap.sum_pay,0)
  //                                               AND DATE_FORMAT(ap.invoice_date,'%Y-%m') = '".$date."'
  //                                               GROUP BY id) v ON v.id = aa.account_id
		// 							LEFT JOIN (SELECT ifnull(sum(uil.line_amt),0) curchg,ui.customer_id id FROM ubs_invoices ui 
  //                                               INNER JOIN ubs_invoice_lines uil ON uil.invoice_id = ui.invoice_id
  //                                               WHERE DATE_FORMAT(ui.invoice_date,'%Y-%m') = '".$date."'
  //                                               AND ui.invoice_num IN (SELECT bill_id FROM ubs_meter_readings)
  //                                               AND ui.invoice_id NOT IN (SELECT invoice_id FROM ar_payments GROUP BY account_id)
  //                                               GROUP BY ui.invoice_id) x ON x.id = aa.account_id
		// 			                     WHERE LAST_DAY(STR_TO_DATE('".$date."', '%Y-%m'))
  //                                        BETWEEN LAST_DAY(aa.active_start)
  //                                        AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))
		// 			                     AND aa.enable_flag = 'Y'
		// 						    GROUP BY aea.zone_1");
		
		$query = $this->db->query("SELECT * FROM mv_collection_prog WHERE yearMonth = '".$date."' ");
		return $query->result();
		//echo $query->result();
	}
	/*AND ucit.invoice_id NOT IN (SELECT DISTINCT invoice_id FROM ar_payments WHERE invoice_total = pay_amount)*/

	public function get_pnd($date,$zone){
        /*$query = $this->db->query("SELECT aa.acct_num,
									      aa.acct_name,
								          (ulpv.bill_total - ulpv.pay_total) as zone_1
									FROM ar_account aa
                                    INNER JOIN ar_entity_address aea ON aea.address_id = aa.acct_addr
                                    INNER JOIN ubs_latest_payments_v ulpv ON aa.account_id = ulpv.account_id 
								    WHERE DATE_FORMAT(ulpv.latest_bill_date,'%Y-%m') = '".$date."'
                                       AND ulpv.bill_total <> ulpv.pay_total
                                       AND aa.enable_flag = 'Y'
								       AND LAST_DAY(STR_TO_DATE('".$date."', '%Y-%m'))
                                       BETWEEN LAST_DAY(aa.active_start)
                                       AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))
								       AND aea.zone_1 = '".$zone."'
								       GROUP BY aa.acct_num,aa.acct_name
								       ORDER BY aa.acct_name ASC"); */
		$query = $this->db->query("SELECT aa.acct_num,
									      aa.acct_name,
								          x.curchg as zone_1
									FROM ar_account aa
                                    INNER JOIN ar_entity_address aea ON aea.address_id = aa.acct_addr
                                    LEFT JOIN (SELECT uil.invoice_total curchg, ui.customer_id id 
                                    		    FROM ubs_invoices ui 
                                                INNER JOIN ubs_invoice_total_v uil ON uil.invoice_id = ui.invoice_id
                                                WHERE DATE_FORMAT(ui.invoice_date,'%Y-%m') = '".$date."'
                                                ) x ON x.id = aa.account_id
                                    LEFT JOIN ubs_invoices ui ON ui.customer_id = aa.account_id
								    WHERE 1=1
                                       AND DATE_FORMAT(ui.invoice_date,'%Y-%m') = '".$date."'
                                       AND ui.invoice_num IN (SELECT bill_id FROM ubs_meter_readings)
                                       AND ui.invoice_id NOT IN (SELECT invoice_id FROM ar_payments GROUP BY account_id) 
                                       AND aa.enable_flag = 'Y'
								       AND LAST_DAY(STR_TO_DATE('".$date."', '%Y-%m'))
                                       BETWEEN LAST_DAY(aa.active_start)
                                       AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))
								       AND aea.zone_1 = '".$zone."'
								       GROUP BY aa.acct_num,aa.acct_name,x.id
								       ORDER BY aa.acct_name ASC");
		return $query->result();
		var_dump($query->result());
	}

	public function get_email($type) {
		$this->db->where('mail_interval_type', $type);
		$this->db->where('enable_flag', 'Y');
		$query = $this->db->get('email');
		return $query->result();
	}
	public function update_sentemail($id) {
		$this->db->where('email_id', $id);
		$this->db->update('email', array('is_sent' => 1));
	}
}